from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from flask_login import UserMixin
import hashlib

# 初始化数据库实例
db = SQLAlchemy()

# 用户模型
class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)
    password = db.Column(db.String(32), nullable=False)  # MD5是32位字符串
    role = db.Column(db.String(20), nullable=False, default='user')  # 权限字段，默认普通用户
    # relate = db.Column(db.String(20), nullable=True)  # 关联字段， nullable=True 允许为空
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    def __repr__(self):
        return f'<User {self.username}>'
    
    # 设置密码（进行MD5哈希）
    def set_password(self, password):
        # 创建MD5哈希对象并更新密码
        md5_hash = hashlib.md5()
        # 将密码转换为字节并更新哈希对象
        md5_hash.update(password.encode('utf-8'))
        # 获取十六进制哈希值
        self.password = md5_hash.hexdigest()
    
    # 验证密码
    def check_password(self, password):
        # 对输入的密码进行同样的哈希处理
        md5_hash = hashlib.md5()
        md5_hash.update(password.encode('utf-8'))
        return self.password == md5_hash.hexdigest()

# 考试成绩模型
# class ExamScore(db.Model):
#     id = db.Column(db.Integer, primary_key=True)
#     user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
#     exam_name = db.Column(db.String(100), nullable=False)
#     exam_date = db.Column(db.String(20), nullable=False)
#     subject = db.Column(db.String(50), nullable=False)
#     score = db.Column(db.Float, nullable=False)
#     grade = db.Column(db.String(10), nullable=False)
#     rank = db.Column(db.Integer, nullable=False)
#     created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
#     def __repr__(self):
#         return f'<ExamScore {self.exam_name} - {self.subject}>'

class ExamProject(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), nullable=False)
    url = db.Column(db.String(100), nullable=False)

    chinese_top = db.Column(db.Float, nullable=False)
    chinese_average = db.Column(db.Float, nullable=False)
    maths_top = db.Column(db.Float, nullable=False)
    maths_average = db.Column(db.Float, nullable=False)
    english_top = db.Column(db.Float, nullable=False)
    english_average = db.Column(db.Float, nullable=False)
    physics_top = db.Column(db.Float, nullable=False)
    physics_average = db.Column(db.Float, nullable=False)
    chemistry_top = db.Column(db.Float, nullable=False)
    chemistry_average = db.Column(db.Float, nullable=False)
    biology_top = db.Column(db.Float, nullable=False)
    biology_average = db.Column(db.Float, nullable=False)

    three_top = db.Column(db.Float, nullable=False)     # 三门课程的最高分
    three_average = db.Column(db.Float, nullable=False) # 三门课程的平均分

    all_original_top = db.Column(db.Float, nullable=False)  # 六门原始分的最高分
    all_grade_top = db.Column(db.Float)                     # 六门等级分的最高分

    all_original_average = db.Column(db.Float, nullable=False)  # 六门原始分的平均分
    all_grade_average = db.Column(db.Float)                     # 六门等级分的平均分

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class ExamScore(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    project_id = db.Column(db.Integer,nullable=False)
    student_xuehao = db.Column(db.String(10), nullable=False)
    student_name = db.Column(db.String(10), nullable=False)
    subject = db.Column(db.String(50), nullable=True)     # 物化生 科目

    chinese_score = db.Column(db.String(10), nullable=False)
    chinese_ranking = db.Column(db.String(10), nullable=True)

    maths_score = db.Column(db.String(10), nullable=False)
    maths_ranking = db.Column(db.String(10), nullable=True)

    english_score = db.Column(db.String(10), nullable=False)
    english_ranking = db.Column(db.String(10), nullable=True)

    physics_score = db.Column(db.String(10), nullable=False)
    physics_ranking = db.Column(db.String(10), nullable=True)
    
    chemistry_original_score = db.Column(db.String(10), nullable=False)
    chemistry_grade_score = db.Column(db.String(10), nullable=True)
    chemistry_ranking = db.Column(db.String(10), nullable=True)

    biology_original_score = db.Column(db.String(10), nullable=False)
    biology_grade_score = db.Column(db.String(10), nullable=True)
    biology_ranking = db.Column(db.String(10), nullable=True)

    all_original_score = db.Column(db.String(10), nullable=True)  # 六门原始分
    all_grade_score = db.Column(db.String(10), nullable=True)     # 六门等级分

    three_score = db.Column(db.String(10), nullable=True)         # 语数外三门合计分
    three_ranking = db.Column(db.String(10), nullable=True)       # 语数外三门年级排名

    class_ranking = db.Column(db.String(10), nullable=True)       # 班级排名
    all_ranking = db.Column(db.String(10), nullable=True)         # 物化生年级排名

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


# 性能指标模型
class PerformanceMetric(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    avg_score = db.Column(db.Float, nullable=False)
    avg_score_change = db.Column(db.String(10), nullable=False)
    max_score = db.Column(db.Float, nullable=False)
    max_score_change = db.Column(db.String(10), nullable=False)
    min_score = db.Column(db.Float, nullable=False)
    min_score_change = db.Column(db.String(10), nullable=False)
    class_rank = db.Column(db.Integer, nullable=False)
    rank_change = db.Column(db.String(20), nullable=False)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    def __repr__(self):
        return f'<PerformanceMetric User {self.user_id}>'

# 数据库初始化函数
def init_db(app):
    # 配置数据库
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///exam.db'
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    
    # 绑定数据库到应用
    db.init_app(app)
    
    with app.app_context():
        db.create_all()
        # 检查是否有测试用户，如果没有则创建
        test_user = User.query.filter_by(email='test@example.com').first()
        if not test_user:
            test_user = User(username='admin', email='test@example.com', role='admin')
            test_user.set_password('123')  # 使用MD5哈希密码
            db.session.add(test_user)
            db.session.commit()
            
            # # 为测试用户添加性能指标
            # test_performance = PerformanceMetric(
            #     user_id=test_user.id,
            #     avg_score=83,
            #     avg_score_change="+2.5%",
            #     max_score=92,
            #     max_score_change="+4.0%",
            #     min_score=72,
            #     min_score_change="-1.5%",
            #     class_rank=12,
            #     rank_change="提升2名"
            # )
            # db.session.add(test_performance)
            # db.session.commit()
        
            
            # 创建示例考试项目
            test_project = ExamProject(
                name="2024年期中考试",
                url="http://example.com/exam1",
                chinese_top=98,
                chinese_average=82.5,
                maths_top=100,
                maths_average=85.3,
                english_top=95,
                english_average=88.7,
                physics_top=92,
                physics_average=78.9,
                chemistry_top=89,
                chemistry_average=76.2,
                biology_top=94,
                biology_average=80.5,
                three_top=293,
                three_average=256.5,
                all_original_top=578,
                all_original_average=500.8
            )
            db.session.add(test_project)
            db.session.flush()  # 获取project_id
            
            # 创建示例成绩
            test_score = ExamScore(
                project_id=test_project.id,
                student_xuehao="2024001",
                student_name="admin",
                chinese_score="88",
                chinese_ranking="10",
                maths_score="92",
                maths_ranking="5",
                english_score="85",
                english_ranking="12",
                physics_score="78",
                physics_ranking="15",
                chemistry_original_score="82",
                chemistry_ranking="13",
                biology_original_score="86",
                biology_ranking="8",
                three_score="265",
                all_original_score="509",
                class_ranking="8"
            )
            db.session.add(test_score)
            
            db.session.commit()

# 数据访问函数
def get_user_by_username(username):
    return User.query.filter_by(username=username).first()

def get_user_by_email(email):
    return User.query.filter_by(email=email).first()

def get_user_by_id(user_id):
    return User.query.get(int(user_id))

def get_user_scores(user_id, exam_type='all', subject='all'):
    query = ExamScore.query.filter_by(user_id=user_id)
    
    # 根据考试类型过滤
    if exam_type != 'all':
        if exam_type == 'midterm':
            query = query.filter(ExamScore.exam_name.contains('期中'))
        elif exam_type == 'final':
            query = query.filter(ExamScore.exam_name.contains('期末'))
        elif exam_type == 'monthly':
            query = query.filter(ExamScore.exam_name.contains('月考'))
        elif exam_type == 'quiz':
            query = query.filter(ExamScore.exam_name.contains('测验'))
        else:
            # 支持通过关键词匹配
            query = query.filter(ExamScore.exam_name.contains(exam_type))
    
    # 根据科目过滤（映射科目英文到中文）
    if subject != 'all':
        subject_map = {
            'math': '数学',
            'chinese': '语文',
            'english': '英语',
            'physics': '物理',
            'chemistry': '化学',
            'biology': '生物',
            'history': '历史',
            'geography': '地理',
            'politics': '政治'
        }
        chinese_subject = subject_map.get(subject, subject)
        query = query.filter_by(subject=chinese_subject)
    
    return query.all()

def get_user_performance(user_id):
    return PerformanceMetric.query.filter_by(user_id=user_id).first()

def get_all_user_scores(user_id):
    return ExamScore.query.filter_by(user_id=user_id).all()

def add_score(user_id, exam_name, exam_date, subject, score, grade, rank):
    new_score = ExamScore(
        user_id=user_id,
        exam_name=exam_name,
        exam_date=exam_date,
        subject=subject,
        score=score,
        grade=grade,
        rank=rank
    )
    db.session.add(new_score)
    db.session.commit()
    return new_score

def update_score(score_id, user_id, data):
    score = ExamScore.query.filter_by(id=score_id, user_id=user_id).first()
    if score:
        score.exam_name = data['exam_name']
        score.exam_date = data['exam_date']
        score.subject = data['subject']
        score.score = data['score']
        score.grade = data['grade']
        score.rank = data['rank']
        db.session.commit()
    return score

def delete_score(score_id, user_id):
    score = ExamScore.query.filter_by(id=score_id, user_id=user_id).first()
    if score:
        db.session.delete(score)
        db.session.commit()
        return True
    return False

def update_performance(user_id, data):
    performance = PerformanceMetric.query.filter_by(user_id=user_id).first()
    
    if not performance:
        # 如果不存在，创建新的性能指标记录
        performance = PerformanceMetric(user_id=user_id)
        db.session.add(performance)
    
    performance.avg_score = data['avg_score']
    performance.avg_score_change = data['avg_score_change']
    performance.max_score = data['max_score']
    performance.max_score_change = data['max_score_change']
    performance.min_score = data['min_score']
    performance.min_score_change = data['min_score_change']
    performance.class_rank = data['class_rank']
    performance.rank_change = data['rank_change']
    performance.updated_at = datetime.utcnow()
    
    db.session.commit()
    return performance